<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of kemiskinan_model
 *
 * @author mozar
 */
if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class kemiskinan_model extends CI_model {

    function __construct() {
        parent::__construct();
        $this->load->database();
    }

    public function getChart($id_kec = 0, $id_kel = 0) {
        $query = '';
        $sql = '';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            if ($id_kel != 0 && strlen($id_kel) > 0 && is_numeric($id_kel)) {
                $sql = "select sum(laki) as jumlah_laki, sum(perempuan) as jumlah_perempuan, kode_kecamatan as kategori_id,"
                        . "nama_kecamatan as kategori from miskin_agregat_jenis_kelamin m "
                        . "where kode_kecamatan=$id_kec  "
                        . "group by kode_kecamatan "
                        . "order by kode_kecamatan";
                //echo $sql;
                $query = $this->db->query($sql);
            } else {
                $sql = "select sum(laki) as jumlah_laki, sum(perempuan) as jumlah_perempuan, kode_kecamatan as kategori_id,"
                        . "nama_kecamatan as kategori from miskin_agregat_jenis_kelamin m "
                        . "where kode_kecamatan=$id_kec "
                        . "group by kode_kecamatan "
                        . "order by kode_kecamatan";
                //echo $sql;
                $query = $this->db->query($sql);
            }
        } else {
            $sql = "select sum(laki) as jumlah_laki, sum(perempuan) as jumlah_perempuan, kode_kecamatan as kategori_id,"
                    . "nama_kecamatan as kategori from miskin_agregat_jenis_kelamin m "
                    . "group by kode_kecamatan "
                    . "order by kode_kecamatan";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    public function getChartUsia($id_kec = 0, $id_kel = 0) {
        $query = '';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
//            if ($id_kel != 0 && strlen($id_kel) > 0 && is_numeric($id_kel)) {
//                $sql = "select sum(jumlah) as jumlah, kode_kelurahan as kategori_id,"
//                        . "nama_kelurahan as kategori from agregat_miskin_jumlah m "
//                        . "where kode_kecamatan=$id_kec and kode_kelurahan=$id_kel "
//                        . "group by kode_kelurahan "
//                        . "order by kode_kelurahan";
//                //echo $sql;
//                $query = $this->db->query($sql);
//            } else {
            $sql = "select sum(laki) as jumlah_laki, sum(perempuan) as jumlah_perempuan, kode_usia as kategori_id,"
                    . "nama_usia as kategori from miskin_agregat_usia m "
                    . "where kode_kecamatan=$id_kec "
                    . "group by kode_usia "
                    . "order by kode_usia";
            //echo $sql;
            $query = $this->db->query($sql);
//            }
        } else {
            $sql = "select sum(laki) as jumlah_laki, sum(perempuan) as jumlah_perempuan, kode_usia as kategori_id,"
                    . "nama_usia as kategori from miskin_agregat_usia m "
                    . "group by kode_usia "
                    . "order by kode_usia";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        return $query->result();
    }

    function getChartPendidikan($id_kec, $id_kel) {
        $query = '';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $sql = "select sum(jumlah_laki) as jumlah_laki, sum(jumlah_perempuan) as jumlah_perempuan, "
                    . "sum(jumlah_laki+jumlah_perempuan) as jumlah,"
                    . "kode_ijazah as kategori_id,"
                    . "nama_ijazah as kategori from "
                    . "miskin_agregat_ijazah_tertinggi m "
                    . "where kode_kecamatan=$id_kec "
                    . "group by kode_ijazah "
                    . "order by kode_ijazah";
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(jumlah_laki) as jumlah_laki, sum(jumlah_perempuan) as jumlah_perempuan, "
                    . "sum(jumlah_laki+jumlah_perempuan) as jumlah,"
                    . "kode_ijazah as kategori_id,"
                    . "nama_ijazah as kategori "
                    . "from miskin_agregat_ijazah_tertinggi m "
                    . "group by kode_ijazah "
                    . "order by kode_ijazah";
            $query = $this->db->query($sql);
        }
        return $query->result();
    }

    function getChartPekerjaan($id_kec, $id_kel) {
        $query = '';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $sql = "select sum(jumlah_laki) as jumlah_laki, "
                    . "sum(jumlah_perempuan) as jumlah_perempuan, "
                    . "sum(jumlah_perempuan+jumlah_laki) as jumlah,"
                    . "kode_pekerjaan as kategori_id,"
                    . "nama_pekerjaan as kategori from "
                    . "miskin_agregat_lapangan_kerja m "
                    . "where kode_kecamatan=$id_kec and kode_pekerjaan>=0 "
                    . "group by kode_pekerjaan "
                    . "order by kode_pekerjaan";
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(jumlah_laki) as jumlah_laki, "
                    . "sum(jumlah_perempuan) as jumlah_perempuan, "
                    . "sum(jumlah_perempuan+jumlah_laki) as jumlah,"
                    . "kode_pekerjaan as kategori_id,"
                    . "nama_pekerjaan as kategori "
                    . "from miskin_agregat_lapangan_kerja m "
                    . "where kode_pekerjaan>=0 "
                    . "group by kode_pekerjaan "
                    . "order by kode_pekerjaan";
            $query = $this->db->query($sql);
        }
        return $query->result();
    }

    function getChartPernikahan($id_kec, $id_kel) {
        $query = '';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $sql = "select sum(jumlah_laki) as jumlah_laki, "
                    . "sum(jumlah_perempuan) as jumlah_perempuan, "
                    . "sum(jumlah_perempuan+jumlah_laki) as jumlah, "
                    . "kode_pernikahan as kategori_id,"
                    . "nama_pernikahan as kategori "
                    . "from miskin_agregat_pernikahan m "
                    . "where kode_kecamatan=$id_kec "
                    . "group by kode_pernikahan "
                    . "order by kode_pernikahan";
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(jumlah_laki) as jumlah_laki, "
                    . "sum(jumlah_perempuan) as jumlah_perempuan, "
                    . "sum(jumlah_perempuan+jumlah_laki) as jumlah, "
                    . "kode_pernikahan as kategori_id,"
                    . "nama_pernikahan as kategori "
                    . "from miskin_agregat_pernikahan m "
                    . "group by kode_pernikahan "
                    . "order by kode_pernikahan";
            $query = $this->db->query($sql);
        }
        return $query->result();
    }

    function getChartStatusDalamKeluarga($id_kec, $id_kel) {
        $query = '';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $sql = "select sum(jumlah) as jumlah, "
                    . "kode_hubungan as kategori_id,"
                    . "nama_hubungan as kategori "
                    . "from miskin_agregat_hubungan_kk m "
                    . "where kode_kecamatan=$id_kec "
                    . "group by kode_hubungan "
                    . "order by kode_hubungan";
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(jumlah) as jumlah, "
                    . "kode_hubungan as kategori_id,"
                    . "nama_hubungan as kategori from miskin_agregat_hubungan_kk m "
                    . "group by kode_hubungan "
                    . "order by kode_hubungan";
            $query = $this->db->query($sql);
        }
        return $query->result();
    }

    function getChartStatusCacat($id_kec, $id_kel) {
        $query = '';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $sql = "select sum(jumlah_laki) as jumlah_laki, "
                    . "sum(jumlah_perempuan) as jumlah_perempuan, "
                    . "sum(jumlah_perempuan+jumlah_laki) as jumlah, "
                    . "kode_cacat as kategori_id,"
                    . "nama_cacat as kategori from miskin_agregat_jenis_cacat m "
                    . "where kode_kecamatan=$id_kec "
                    . "group by kode_cacat "
                    . "order by kode_cacat";
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(jumlah_laki) as jumlah_laki, sum(jumlah_perempuan) as jumlah_perempuan, "
                    . "sum(jumlah_perempuan+jumlah_laki) as jumlah, "
                    . "kode_cacat as kategori_id,"
                    . "nama_cacat as kategori from miskin_agregat_jenis_cacat m "
                    . "group by kode_cacat "
                    . "order by kode_cacat";
            $query = $this->db->query($sql);
        }
        return $query->result();
    }

    function getChartStatusKeaksaraan($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'keaksaraan';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket2 as kategori "
                    . " from miskin_warga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket2 as kategori "
                    . " from miskin_warga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartKepemilikanKartuId($id_kec, $id_kel) {
        $query = '';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $sql = "select sum(jumlah_laki) as jumlah_laki, sum(jumlah_perempuan) as jumlah_perempuan, "
                    . "sum(jumlah_perempuan+jumlah_laki) as jumlah, "
                    . "kode_kepemilikan as kategori_id,"
                    . "nama_kepemilikan as kategori from miskin_agregat_kepemilikan_kartu_identitas m "
                    . "where kode_kecamatan=$id_kec "
                    . "group by kode_kepemilikan "
                    . "order by kode_kepemilikan";
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(jumlah_laki) as jumlah_laki, sum(jumlah_perempuan) as jumlah_perempuan, "
                    . "sum(jumlah_perempuan+jumlah_laki) as jumlah, "
                    . "kode_kepemilikan as kategori_id,"
                    . "nama_kepemilikan as kategori from miskin_agregat_kepemilikan_kartu_identitas m "
                    . "group by kode_kepemilikan "
                    . "order by kode_kepemilikan";
            $query = $this->db->query($sql);
        }
        return $query->result();
    }

    function getChartPartisipasiSekolah($id_kec, $id_kel) {
        $query = '';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $sql = "select sum(jumlah_laki) as jumlah_laki, sum(jumlah_perempuan) as jumlah_perempuan, "
                    . "sum(jumlah_perempuan+jumlah_laki) as jumlah, "
                    . "kode_partisipasi as kategori_id,"
                    . "nama_partisipasi as kategori "
                    . "from miskin_agregat_partisipasi_sekolah m "
                    . "where kode_kecamatan=$id_kec "
                    . "group by kode_partisipasi "
                    . "order by kode_partisipasi";
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(jumlah_laki) as jumlah_laki, sum(jumlah_perempuan) as jumlah_perempuan, "
                    . "sum(jumlah_perempuan+jumlah_laki) as jumlah, "
                    . "kode_partisipasi as kategori_id,"
                    . "nama_partisipasi as kategori from miskin_agregat_partisipasi_sekolah m "
                    . "group by kode_partisipasi "
                    . "order by kode_partisipasi";
            $query = $this->db->query($sql);
        }
        return $query->result();
    }

    function getChartPenyakitKronis($id_kec, $id_kel) {
        $query = '';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $sql = "select sum(jumlah_laki) as jumlah_laki, sum(jumlah_perempuan) as jumlah_perempuan, "
                    . "sum(jumlah_perempuan+jumlah_laki) as jumlah, "
                    . "kode_penyakit as kategori_id,"
                    . "nama_penyakit as kategori from miskin_agregat_penyakit_kronis m "
                    . "where kode_kecamatan=$id_kec "
                    . "group by kode_penyakit "
                    . "order by kode_penyakit";
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(jumlah_laki) as jumlah_laki, sum(jumlah_perempuan) as jumlah_perempuan, "
                    . "sum(jumlah_perempuan+jumlah_laki) as jumlah, "
                    . "kode_penyakit as kategori_id,"
                    . "nama_penyakit as kategori from miskin_agregat_penyakit_kronis m "
                    . "group by kode_penyakit "
                    . "order by kode_penyakit";
            $query = $this->db->query($sql);
        }
        return $query->result();
    }

    function getChartKeterangan($id_kec, $id_kel) {
        $query = '';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $sql = "select sum(laki) as jumlah_laki, sum(perempuan) as jumlah_perempuan, "
                    . "sum(laki+perempuan) as jumlah,"
                    . "kode_keterangan as kategori_id,"
                    . "ms.ket as kategori from miskin_agregat_keterangan m "
                    . "inner join "
                    . "miskin_master_keterangan ms on ms.kode=m.kode_keterangan "
                    . "where kode_kecamatan=$id_kec "
                    . "group by kode_keterangan "
                    . "order by kode_keterangan";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(laki) as jumlah_laki, sum(perempuan) as jumlah_perempuan, "
                    . "sum(laki+perempuan) as jumlah,"
                    . "kode_keterangan as kategori_id,"
                    . "ms.ket as kategori from miskin_agregat_keterangan m inner join "
                    . "miskin_master_keterangan ms on ms.kode=m.kode_keterangan "
                    . "group by kode_keterangan "
                    . "order by kode_keterangan";
            //echo $sql;
            $query = $this->db->query($sql);
        }

        return $query->result();
    }

    function getChartUsiaProduktif($id_kec, $id_kel) {
        $query = '';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $sql = "select sum(jumlah_miskin) as jumlah_balita, sum(jumlah_total) "
                    . "as jumlah_total, "
                    . " kode_kecamatan as kategori_id, nama_kecamatan as kategori "
                    . " from miskin_agregat_usia_produktif m "
                    . "where kode_kecamatan=$id_kec "
                    . "group by kode_kecamatan "
                    . "order by kode_kecamatan";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(jumlah_miskin) as jumlah_balita, sum(jumlah_total) as jumlah_total,"
                    . " kode_kecamatan as kategori_id, nama_kecamatan as kategori "
                    . " from miskin_agregat_usia_produktif m "
                    . "group by kode_kecamatan "
                    . "order by kode_kecamatan";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        return $query->result();
    }

    function get_list_kelurahan($id_kec) {
        $this->db->select('id_kelurahan, nama_kelurahan');
        $this->db->where(array('fid_kecamatan' => $id_kec));
        return $this->db->get('miskin_kelurahan')->result();
    }

    function getChartKepimilkanTempatTinggal($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            if ($id_kel != 0 && strlen($id_kel) > 0 && is_numeric($id_kel) && false) {
                $sql = "select sum(1) as jumlah, "
                        . " status_tempat_tinggal as kategori_id, "
                        . " q.ket as kategori "
                        . " from miskin_survey_pokok_rumahtangga m "
                        . "inner join miskin_master_status_kepemilikan_rumah q "
                        . "on q.kode=m.status_tempat_tinggal inner join miskin_rumahtangga rt "
                        . "on rt.id_rt=m.fid_keluarga inner join miskin_kelurahan kl "
                        . "on kl.fid_kecamatan=m.kode_kecamatan "
                        . "where m.kode_kecamatan=$id_kec "
                        . "and kl.id_kelurahan=$id_kel "
                        . "group by status_tempat_tinggal "
                        . "order by status_tempat_tinggal";
                //echo $sql;
                //$query = $this->db->query($sql);
            } else {
                $sql = "select sum(1) as jumlah, "
                        . " status_tempat_tinggal as kategori_id, "
                        . " q.ket as kategori "
                        . " from miskin_survey_pokok_rumahtangga m "
                        . "inner join miskin_master_status_kepemilikan_rumah q "
                        . "on q.kode=m.status_tempat_tinggal "
                        . "where kode_kecamatan=$id_kec "
                        . "group by status_tempat_tinggal "
                        . "order by status_tempat_tinggal";
                //echo $sql;
                $query = $this->db->query($sql);
            }
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " status_tempat_tinggal as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join miskin_master_status_kepemilikan_rumah q "
                    . "on q.kode=m.status_tempat_tinggal "
                    . "group by status_tempat_tinggal "
                    . "order by status_tempat_tinggal";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        return $query->result();
    }

    function getChartLuasTanah($id_kec, $id_kel) {
        $query = '';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $sql = "select sum(jumlah) as jumlah ,kode_kategori as kategori_id, "
                    . "nama_kategori as kategori, min_val as min, max_val as max "
                    . " from miskin_agregat_rumahtangga_luastanah m "
                    . "where kode_kecamatan=$id_kec "
                    . "group by kode_kategori "
                    . "order by kode_kategori";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(jumlah) as jumlah ,kode_kategori as kategori_id, "
                    . "nama_kategori as kategori, min_val as min, max_val as max "
                    . " from miskin_agregat_rumahtangga_luastanah m "
                    . "group by kode_kategori "
                    . "order by kode_kategori";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        return $query->result();
    }

    function getChartJenisLantai($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " jenis_lantai as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join miskin_master_jenis_lantai q "
                    . "on q.kode=m.jenis_lantai "
                    . "where kode_kecamatan=$id_kec "
                    . "group by jenis_lantai "
                    . "order by jenis_lantai";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " jenis_lantai as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join miskin_master_jenis_lantai q "
                    . "on q.kode=m.jenis_lantai "
                    . "group by jenis_lantai "
                    . "order by jenis_lantai";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        return $query->result();
    }

    function getChartJenisDinding($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " jenis_dinding as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join miskin_master_jenis_dinding q "
                    . "on q.kode=m.jenis_dinding "
                    . "where kode_kecamatan=$id_kec "
                    . "group by jenis_dinding "
                    . "order by jenis_dinding";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " jenis_dinding as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join miskin_master_jenis_dinding q "
                    . "on q.kode=m.jenis_dinding "
                    . "group by jenis_dinding "
                    . "order by jenis_dinding";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        return $query->result();
    }

    function getChartJenisAtap($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " jenis_atap as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join miskin_master_jenis_atap q "
                    . "on q.kode=m.jenis_atap "
                    . "where kode_kecamatan=$id_kec "
                    . "group by jenis_atap "
                    . "order by jenis_atap";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " jenis_atap as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join miskin_master_jenis_atap q "
                    . "on q.kode=m.jenis_atap "
                    . "group by jenis_atap "
                    . "order by jenis_atap";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        return $query->result();
    }

    function getChartSumberMinum($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " sumber_air_minum as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join miskin_master_sumber_minum q "
                    . "on q.kode=m.sumber_air_minum "
                    . "where kode_kecamatan=$id_kec "
                    . "group by sumber_air_minum "
                    . "order by sumber_air_minum";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " sumber_air_minum as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join miskin_master_sumber_minum q "
                    . "on q.kode=m.sumber_air_minum "
                    . "group by sumber_air_minum "
                    . "order by sumber_air_minum";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        return $query->result();
    }

    function getChartCaraDapatMinum($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " cara_memperoleh_air_minum as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join miskin_master_cara_dapat_minum q "
                    . "on q.kode=m.cara_memperoleh_air_minum "
                    . "where kode_kecamatan=$id_kec "
                    . "group by cara_memperoleh_air_minum "
                    . "order by cara_memperoleh_air_minum";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " cara_memperoleh_air_minum as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join miskin_master_cara_dapat_minum q "
                    . "on q.kode=m.cara_memperoleh_air_minum "
                    . "group by cara_memperoleh_air_minum "
                    . "order by cara_memperoleh_air_minum";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        return $query->result();
    }

    function getChartSumberPenerangan($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'sumber_penerangan';
        $tabel = 'miskin_master_sumber_penerangan';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        return $query->result();
    }

    function getChartEnergiMasak($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'energi_memasak';
        $tabel = 'miskin_master_energi_memasak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartFasilitasBAB($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'penggunaan_fasilitas_bab';
        $tabel = 'miskin_master_' . $kolom1;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartPembuanganBAB($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'tempat_pembuangan_tinja';
        $tabel = 'miskin_master_' . $kolom1;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartMerokok($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'merokok';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket2 as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket2 as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartMobil($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'punya_mobil';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartKapalMotor($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'punya_kapal_motor';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartPerahuMotor($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'punya_perahu_motor';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartSepedaMotor($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'punya_sepeda_motor';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartSepeda($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'punya_sepeda';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartPerahu($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'punya_perahu';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartKulkas($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'punya_kulkas';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartElpiji($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'punya_lpg';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartHape($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'punya_hp';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartPKH($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'ikut_pkh';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, "
                    . " $kolom1 as kategori_id, "
                    . " q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q "
                    . "on q.kode=m.$kolom1 "
                    . "group by $kolom1 "
                    . "order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartRaskin($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'ikut_raskin';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartJamkesmas($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'ikut_jamkesmas';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartAskes($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'ikut_askes';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartJamsostek($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'ikut_jamsostek';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartKB($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'ikut_kb';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartBeasiswaMiskin($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'ikut_beasiswa_miskin';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartBlsm($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'ikut_blsm';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartSPM($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'ikut_spm';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartJampersal($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'ikut_jampersal';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartBedahRumah($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'ikut_bedahrumah';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function getChartProgramLain($id_kec, $id_kel) {
        $query = '';
        $id_kec = (int) $id_kec;
        $id_kel = (int) $id_kel;
        $kolom1 = 'ikut_program_lain2';
        $tabel = 'miskin_master_yatidak';
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {

            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "where kode_kecamatan=$id_kec "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        } else {
            $sql = "select sum(1) as jumlah, $kolom1 as kategori_id, q.ket as kategori "
                    . " from miskin_survey_pokok_rumahtangga m "
                    . "inner join $tabel q on q.kode=m.$kolom1 "
                    . "group by $kolom1 order by $kolom1";
            //echo $sql;
            $query = $this->db->query($sql);
        }
        //echo $sql;
        return $query->result();
    }

    function get_data_penduduk($kunci, $limit, $halaman, $id_kec, $id_kel, $kolom_filter) {
        $filter = '';
        if (strlen($id_kec) > 0 && is_numeric($id_kec) && $id_kec > 0) {
            $id_kec = (int) $id_kec;
            if (strlen($id_kel) > 0 && is_numeric($id_kel) && $id_kel > 0) {
                $id_kel = (int) $id_kel;
                if (strlen($kunci) > 0 && is_numeric($kunci) && $kunci >= 0) {
                    $filter = " and $kolom_filter=$kunci ";
                }
//                $sql = "select *, $kolom_filter as kategori_id from miskin_warga where "
//                        . "kode_kecamatan=$id_kec $filter and "
//                        . "fid_keluarga "
//                        . "in ("
//                        . "select id_rt "
//                        . "from miskin_rumahtangga "
//                        . "where kecamatan=$id_kec and kelurahan=$id_kel "
//                        . ") "
//                        . "limit $halaman, $limit";
                $sql = "select m.*,t.*, m.$kolom_filter as kategori_id "
                        . "from miskin_warga m "
                        . "inner join miskin_rumahtangga t "
                        . "on m.fid_keluarga=t.id_rt "
                        . "and t.kode_kecamatan=m.kode_kecamatan "
                        . "where t.kode_kecamatan=$id_kec $filter "
                        . "and t.kelurahan=$id_kel "
                        . "limit $halaman, $limit";
                //echo $sql;
                $query = $this->db->query($sql);
                return $query->result();
            } else {
                $jenis_kelamin = '';
                if (strlen($kunci) > 0 && is_numeric($kunci) && $kunci >= 0) {
                    $filter = " and $kolom_filter=$kunci ";
                }
                $sql = "select *, $kolom_filter as kategori_id from miskin_warga  where "
                        . "kode_kecamatan=$id_kec $filter "
                        . "limit $halaman, $limit";
                $sql = "select m.*,t.*, m.$kolom_filter as kategori_id "
                        . "from miskin_warga m "
                        . "inner join miskin_rumahtangga t "
                        . "on m.fid_keluarga=t.id_rt "
                        . "and t.kode_kecamatan=m.kode_kecamatan "
                        . "where t.kode_kecamatan=$id_kec $filter "
                        . "limit $halaman, $limit";
                //echo $sql;
                $query = $this->db->query($sql);
                return $query->result();
            }
        } else {
            $jenis_kelamin = '';
            if (strlen($kunci) > 0 && is_numeric($kunci) && $kunci >= 0) {
                $filter = " where $kolom_filter=$kunci ";
            }
            $sql = "select *, $kolom_filter as kategori_id from miskin_warga  $filter "
                    . "limit $halaman, $limit";
            $sql = "select m.*,t.*, m.$kolom_filter as kategori_id "
                    . "from miskin_warga m "
                    . "inner join miskin_rumahtangga t "
                    . "on m.fid_keluarga=t.id_rt "
                    . "and t.kode_kecamatan=m.kode_kecamatan "
                    . " $filter "
                    . "limit $halaman, $limit";
            //echo $sql;
            $query = $this->db->query($sql);
            return $query->result();
        }
    }

    function get_jumlah_data($kunci, $id_kec, $id_kel, $kolom_filter) {
        $filter = '';
        if (strlen($id_kec) > 0 && is_numeric($id_kec) && $id_kec > 0) {
            $id_kec = (int) $id_kec;
            if (strlen($id_kel) > 0 && is_numeric($id_kel) && $id_kel > 0) {
                $id_kel = (int) $id_kel;
                if (strlen($kunci) > 0 && is_numeric($kunci) && $kunci >= 0) {
                    $filter = " and $kolom_filter=$kunci ";
                }
                $sql = "select count(*) as jumlah from miskin_warga where "
                        . "kode_kecamatan=$id_kec $filter and "
                        . "fid_keluarga "
                        . "in ("
                        . "select id_rt "
                        . "from miskin_rumahtangga "
                        . "where kecamatan=$id_kec and kelurahan=$id_kel "
                        . ") ";
                //echo $sql;
                $query = $this->db->query($sql);
                return $query->result()[0]->jumlah;
            } else {
                if (strlen($kunci) > 0 && is_numeric($kunci) && $kunci >= 0) {
                    $filter = " and $kolom_filter=$kunci ";
                }
                $sql = "select count(*) as jumlah from miskin_warga  where "
                        . "kode_kecamatan=$id_kec $filter";
                //echo $sql;
                $query = $this->db->query($sql);
                return $query->result()[0]->jumlah;
            }
        } else {
            if (strlen($kunci) > 0 && is_numeric($kunci) && $kunci >= 0) {
                $filter = " where $kolom_filter=$kunci ";
            }
            $sql = "select count(*) as jumlah from miskin_warga  $filter ";
            //echo $sql;
            $query = $this->db->query($sql);
            return $query->result()[0]->jumlah;
        }
    }

    function get_list_master($tabel, $kolom = 'ket') {
        //$tabel = 'miskin_master_'.$tabel;
        $sql = "select kode as kategori_id, $kolom as kategori from $tabel";
        $q = $this->db->query($sql);
        return $q->result();
    }

    function get_jumlah_data_usia($kunci, $id_kecamatan, $id_kelurahan) {
        $sql = "select * from master_usia where kode=$kunci";
        $q = $this->db->query($sql);
        $min = -9;
        $max = -9;
        foreach ($q->result() as $row) {
            $min = $row->umur_min;
            $max = $row->umur_max;
        }
        return $this->get_jumlah_data_usia2($min, $max, $id_kecamatan, $id_kelurahan);
    }

    function get_jumlah_data_usia2($min, $max, $id_kec, $id_kel) {
        if (strlen($id_kec) > 0 && is_numeric($id_kec) && $id_kec > 0) {
            $id_kec = (int) $id_kec;
            if (strlen($id_kel) > 0 && is_numeric($id_kel) && $id_kel > 0) {
                $id_kel = (int) $id_kel;
                $sql = "select count(*) as jumlah from ("
                        . "select *, TIMESTAMPDIFF(YEAR,tanggal_lahir2,CURDATE()) "
                        . "AS age from miskin_warga where kode_kecamatan=$id_kec and "
                        . "fid_keluarga in ("
                        . "select id_rt "
                        . "from miskin_rumahtangga "
                        . "where kecamatan=$id_kec and kelurahan=$id_kel "
                        . ")"
                        . ")as warga where warga.age>=$min and warga.age<=$max";
                $query = $this->db->query($sql);
                return $query->result()[0]->jumlah;
            } else {
                $sql = "select count(*) as jumlah from ("
                        . "select *, TIMESTAMPDIFF(YEAR,tanggal_lahir2,CURDATE()) "
                        . "AS age from miskin_warga where kode_kecamatan=$id_kec "
                        . ")as warga where warga.age>=$min and warga.age<=$max";
                $query = $this->db->query($sql);
                return $query->result()[0]->jumlah;
            }
        } else {
            $sql = "select count(*) as jumlah from ("
                    . "select *, TIMESTAMPDIFF(YEAR,tanggal_lahir2,CURDATE()) "
                    . "AS age from miskin_warga"
                    . ")as warga where warga.age>=$min and warga.age<=$max";
            $query = $this->db->query($sql);
            return $query->result()[0]->jumlah;
        }
    }

    function get_data_penduduk_usia($kunci, $limit, $halaman, $id_kecamatan, $id_kelurahan) {
        $sql = "select * from master_usia where kode=$kunci";
        $q = $this->db->query($sql);
        $min = -9;
        $max = -9;
        foreach ($q->result() as $row) {
            $min = $row->umur_min;
            $max = $row->umur_max;
        }
        return $this->get_data_penduduk_usia2($min, $max, $limit, $halaman, $id_kecamatan, $id_kelurahan);
    }

    function get_data_penduduk_usia2($min, $max, $limit, $halaman, $id_kec, $id_kel) {

        if (strlen($id_kec) > 0 && is_numeric($id_kec) && $id_kec > 0) {
            $id_kec = (int) $id_kec;
            if (strlen($id_kel) > 0 && is_numeric($id_kel) && $id_kel > 0) {
                $id_kel = (int) $id_kel;
                $sql = "select * from ("
                        . "	select *, TIMESTAMPDIFF(YEAR,tanggal_lahir2,CURDATE()) "
                        . "AS age from miskin_warga where kode_kecamatan=$id_kec and "
                        . "fid_keluarga in ("
                        . "select id_rt "
                        . "from miskin_rumahtangga "
                        . "where kecamatan=$id_kec and kelurahan=$id_kel "
                        . ")"
                        . ")as warga where warga.age>=$min and warga.age<=$max "
                        . "limit $halaman,$limit";
                $sql = "select m.*,t.* from ("
                        . "select *, TIMESTAMPDIFF(YEAR,tanggal_lahir2,CURDATE()) "
                        . "as age "
                        . "from miskin_warga  "
                        . ") as m "
                        . "inner join miskin_rumahtangga t "
                        . "on m.fid_keluarga=t.id_rt "
                        . "and t.kode_kecamatan=m.kode_kecamatan "
                        . "where t.kode_kecamatan=$id_kec "
                        . "and t.kelurahan=$id_kel "
                        . "and "
                        . "m.age>=$min and m.age<=$max "
                        . "limit $halaman, $limit";
                //echo $sql;
                $query = $this->db->query($sql);
                return $query->result();
            } else {
                $sql = "select * from ("
                        . "	select *, TIMESTAMPDIFF(YEAR,tanggal_lahir2,CURDATE()) "
                        . "AS age from miskin_warga where kode_kecamatan=$id_kec "
                        . ")as warga where warga.age>=$min and warga.age<=$max "
                        . "limit $halaman,$limit";
                $sql = "select m.*,t.* from ("
                        . "select *, TIMESTAMPDIFF(YEAR,tanggal_lahir2,CURDATE()) "
                        . "as age "
                        . "from miskin_warga  "
                        . ") as m "
                        . "inner join miskin_rumahtangga t "
                        . "on m.fid_keluarga=t.id_rt "
                        . "and t.kode_kecamatan=m.kode_kecamatan "
                        . "where t.kode_kecamatan=$id_kec "
                        . "and "
                        . "m.age>=$min and m.age<=$max "
                        . "limit $halaman, $limit";
                //echo $sql;
                $query = $this->db->query($sql);
                return $query->result();
            }
        } else {
            $sql = "select * from ("
                    . "	select *, TIMESTAMPDIFF(YEAR,tanggal_lahir2,CURDATE()) "
                    . "AS age from miskin_warga"
                    . ")as warga where warga.age>=$min and warga.age<=$max "
                    . "limit $halaman,$limit";
            $sql = "select m.*,t.* from ("
                    . "select *, TIMESTAMPDIFF(YEAR,tanggal_lahir2,CURDATE()) "
                    . "as age "
                    . "from miskin_warga  "
                    . ") as m "
                    . "inner join miskin_rumahtangga t "
                    . "on m.fid_keluarga=t.id_rt "
                    . "and t.kode_kecamatan=m.kode_kecamatan "
                    . "where "
                    . "m.age>=$min and m.age<=$max "
                    . "limit $halaman, $limit";
            //echo $sql;
            $query = $this->db->query($sql);
            return $query->result();
        }
    }

    function get_jumlah_data_rt($kunci, $id_kec, $id_kel, $kolom_filter) {
        $filter = '';
        if (strlen($id_kec) > 0 && is_numeric($id_kec) && $id_kec > 0) {
            $id_kec = (int) $id_kec;
            if (strlen($id_kel) > 0 && is_numeric($id_kel) && $id_kel > 0) {
                $id_kel = (int) $id_kel;
                if (strlen($kunci) > 0 && is_numeric($kunci) && $kunci >= 0) {
                    $filter = " and $kolom_filter=$kunci ";
                }
                $sql = "select count(*) as jumlah "
                        . "from miskin_survey_pokok_rumahtangga where "
                        . "kode_kecamatan=$id_kec $filter and "
                        . "fid_keluarga "
                        . "in ("
                        . "select id_rt "
                        . "from miskin_rumahtangga "
                        . "where kecamatan=$id_kec and kelurahan=$id_kel "
                        . ") ";
                //echo $sql;
                $query = $this->db->query($sql);
                return $query->result()[0]->jumlah;
            } else {
                if (strlen($kunci) > 0 && is_numeric($kunci) && $kunci >= 0) {
                    $filter = " and $kolom_filter=$kunci ";
                }
                $sql = "select count(*) as jumlah from miskin_survey_pokok_rumahtangga  where "
                        . "kode_kecamatan=$id_kec $filter";
                //echo $sql;
                $query = $this->db->query($sql);
                return $query->result()[0]->jumlah;
            }
        } else {
            if (strlen($kunci) > 0 && is_numeric($kunci) && $kunci >= 0) {
                $filter = " where $kolom_filter=$kunci ";
            }
            $sql = "select count(*) as jumlah from miskin_survey_pokok_rumahtangga  $filter ";
            //echo $sql;
            $query = $this->db->query($sql);
            return $query->result()[0]->jumlah;
        }
    }

    function get_data_rt($kunci, $limit, $halaman, $id_kec, $id_kel, $kolom_filter) {
        $filter = '';
        if (strlen($id_kec) > 0 && is_numeric($id_kec) && $id_kec > 0) {
            $id_kec = (int) $id_kec;
            if (strlen($id_kel) > 0 && is_numeric($id_kel) && $id_kel > 0) {
                $id_kel = (int) $id_kel;
                if (strlen($kunci) > 0 && is_numeric($kunci) && $kunci >= 0) {
                    $filter = " and $kolom_filter=$kunci ";
                }
                $sql = "select m.*,t.*, m.$kolom_filter as kategori_id "
                        . "from miskin_survey_pokok_rumahtangga m "
                        . "inner join miskin_rumahtangga t "
                        . "on m.fid_keluarga=t.id_rt "
                        . "and t.kode_kecamatan=m.kode_kecamatan "
                        . "where t.kode_kecamatan=$id_kec $filter "
                        . "and t.kelurahan=$id_kel "
                        . "limit $halaman, $limit";
                //echo $sql;
                $query = $this->db->query($sql);
                $arr = array();
                $arr3 = array();
                $query = $this->db->query($sql);
                //return $query->result();
                foreach ($query->result() as $row) {
                    if (!isset($arr[$row->kode_kecamatan])) {
                        $arr[$row->kode_kecamatan] = array($row->fid_keluarga);
                    } else {
                        $arr[$row->kode_kecamatan][] = $row->fid_keluarga;
                    }
                }
                $arr2 = array();
                foreach ($arr as $key => $val) {
                    $kode_kecamatan = $key;
                    $list_id_keluarga = $val;
                    if (count($list_id_keluarga) > 0) {
                        $sql = "select nama_warga,fid_keluarga from miskin_warga where kode_kecamatan=$kode_kecamatan and hubungan_kk=0 and fid_keluarga in (" . implode(",", $list_id_keluarga) . ")";
                        $q = $this->db->query($sql);
                        foreach ($q->result() as $row) {
                            $arr2[$key][$row->fid_keluarga] = $row->nama_warga;
                        }
                    }
                }
                foreach ($query->result() as $row) {
                    $d = array();
                    foreach ($row as $k => $v) {
                        $d[$k] = $v;
                    }
                    $d['kepala_keluarga'] = $arr2[$d['kode_kecamatan']][$d['fid_keluarga']];
                    $arr3[] = $d;
                }
                //var_dump($arr);
                //var_dump($arr2);
                //var_dump($arr3);
                return $arr3;
            } else {
                $jenis_kelamin = '';
                if (strlen($kunci) > 0 && is_numeric($kunci) && $kunci >= 0) {
                    $filter = " and $kolom_filter=$kunci ";
                }
                $sql = "select *, $kolom_filter as kategori_id from miskin_warga  where "
                        . "kode_kecamatan=$id_kec $filter "
                        . "limit $halaman, $limit";
                $sql = "select m.*,t.*, m.$kolom_filter as kategori_id "
                        . "from miskin_survey_pokok_rumahtangga m "
                        . "inner join miskin_rumahtangga t "
                        . "on m.fid_keluarga=t.id_rt "
                        . "and t.kode_kecamatan=m.kode_kecamatan "
                        . "where t.kode_kecamatan=$id_kec $filter "
                        . "limit $halaman, $limit";
                //echo $sql;
                $query = $this->db->query($sql);
                $arr = array();
                $arr3 = array();
                $query = $this->db->query($sql);
                //return $query->result();
                foreach ($query->result() as $row) {
                    if (!isset($arr[$row->kode_kecamatan])) {
                        $arr[$row->kode_kecamatan] = array($row->fid_keluarga);
                    } else {
                        $arr[$row->kode_kecamatan][] = $row->fid_keluarga;
                    }
                }
                $arr2 = array();
                foreach ($arr as $key => $val) {
                    $kode_kecamatan = $key;
                    $list_id_keluarga = $val;
                    if (count($list_id_keluarga) > 0) {
                        $sql = "select nama_warga,fid_keluarga from miskin_warga where kode_kecamatan=$kode_kecamatan and hubungan_kk=0 and fid_keluarga in (" . implode(",", $list_id_keluarga) . ")";
                        $q = $this->db->query($sql);
                        foreach ($q->result() as $row) {
                            $arr2[$key][$row->fid_keluarga] = $row->nama_warga;
                        }
                    }
                }
                foreach ($query->result() as $row) {
                    $d = array();
                    foreach ($row as $k => $v) {
                        $d[$k] = $v;
                    }
                    $d['kepala_keluarga'] = $arr2[$d['kode_kecamatan']][$d['fid_keluarga']];
                    $arr3[] = $d;
                }
                //var_dump($arr);
                //var_dump($arr2);
                //var_dump($arr3);
                return $arr3;
            }
        } else {
            $jenis_kelamin = '';
            if (strlen($kunci) > 0 && is_numeric($kunci) && $kunci >= 0) {
                $filter = " where $kolom_filter=$kunci ";
            }
            $sql = "select *, $kolom_filter as kategori_id from miskin_warga  $filter "
                    . "limit $halaman, $limit";
            $sql = "select m.*,t.*, m.$kolom_filter as kategori_id "
                    . "from miskin_survey_pokok_rumahtangga m "
                    . "inner join miskin_rumahtangga t "
                    . "on m.fid_keluarga=t.id_rt "
                    . "and t.kode_kecamatan=m.kode_kecamatan "
                    . " $filter "
                    . "limit $halaman, $limit";
            //echo "$sql\n";
            $arr = array();
            $arr3 = array();
            $query = $this->db->query($sql);
            //return $query->result();
            foreach ($query->result() as $row) {
                if (!isset($arr[$row->kode_kecamatan])) {
                    $arr[$row->kode_kecamatan] = array($row->fid_keluarga);
                } else {
                    $arr[$row->kode_kecamatan][] = $row->fid_keluarga;
                }
            }
            $arr2 = array();
            foreach ($arr as $key => $val) {
                $kode_kecamatan = $key;
                $list_id_keluarga = $val;
                if (count($list_id_keluarga) > 0) {
                    $sql = "select nama_warga,fid_keluarga from miskin_warga where kode_kecamatan=$kode_kecamatan and hubungan_kk=0 and fid_keluarga in (" . implode(",", $list_id_keluarga) . ")";
                    $q = $this->db->query($sql);
                    foreach ($q->result() as $row) {
                        $arr2[$key][$row->fid_keluarga] = $row->nama_warga;
                    }
                }
            }
            foreach ($query->result() as $row) {
                $d = array();
                foreach ($row as $k => $v) {
                    $d[$k] = $v;
                }
                $d['kepala_keluarga'] = $arr2[$d['kode_kecamatan']][$d['fid_keluarga']];
                $arr3[] = $d;
            }
            //var_dump($arr);
            //var_dump($arr2);
            //var_dump($arr3);
            return $arr3;
        }
    }

    function get_anggota_keluarga($id_keluarga, $id_kec) {
        $id_keluarga = (int) $id_keluarga;
        $id_kec = (int) $id_kec;
        $sql = "select * from miskin_warga where kode_kecamatan=$id_kec and fid_keluarga=$id_keluarga";
        //echo $sql;
        $q = $this->db->query($sql);
        return $q->result();
    }

    function get_detil_keluarga($id_keluarga, $id_kec) {
        $id_keluarga = (int) $id_keluarga;
        $id_kec = (int) $id_kec;
        $sql = "select rt.*,s.* from miskin_rumahtangga rt inner join miskin_survey_pokok_rumahtangga s "
                . "on s.kode_kecamatan=rt.kode_kecamatan and rt.id_rt=s.fid_keluarga "
                . "where rt.kode_kecamatan=$id_kec "
                . "and s.fid_keluarga=$id_keluarga";
        //echo $sql;
        $q = $this->db->query($sql);
        $res = $q->result();
        return $res;
    }

    function get_detil_warga($filter_warga) {
        $sql = "select w.* from miskin_warga w "
                . "where $filter_warga limit 1";
        //echo $sql;
        $q = $this->db->query($sql);
        $hasil = array();
        foreach ($q->result() as $row) {
            foreach ($row as $key => $val) {
                $hasil[$key] = $val;
            }
            break;
        }
        if (count($hasil) > 0) {
            $sql = "select * from miskin_rumahtangga where kode_kecamatan="
                    . $hasil['kode_kecamatan'] . " and id_rt=" . $hasil['fid_keluarga'];
            $q = $this->db->query($sql);
            foreach ($q->result() as $row) {
                foreach ($row as $key => $val) {
                    $hasil[$key] = $val;
                }
                break;
            }
        }
        if (count($hasil) == 0)
            return $hasil;
        return array($hasil);
    }

    function get_jumlah_data_rt_tanah($min, $max, $id_kecamatan, $id_kelurahan) {
        $min = (int) $min;
        $max = (int) $max;
        if ($min > $max)
            $max = $min;
        $sql1 = "select count(*) as jumlah from miskin_survey_pokok_rumahtangga ";
        $id_kecamatan = (int) $id_kecamatan;
        $id_kelurahan = (int) $id_kelurahan;
        if ($id_kecamatan > 0) {
            if ($id_kelurahan > 0) {
                $sql1 = "select count(*) as jumlah from miskin_survey_pokok_rumahtangga "
                        . "where kode_kecamatan=$id_kecamatan and fid_keluarga in ("
                        . "select id_rt from miskin_rumahtangga where kode_kecamatan="
                        . "$id_kecamatan and kelurahan=$id_kelurahan"
                        . ")";
            } else {
                $sql1 = "select count(*) as jumlah from miskin_survey_pokok_rumahtangga "
                        . "where kode_kecamatan=$id_kecamatan ";
            }
        }
        $q = $this->db->query($sql1);
        return $q->result()[0]->jumlah;
    }

    function get_data_rt_tanah($min, $max, $limit, $halaman, $id_kec, $id_kel) {
        $min = (int) $min;
        $max = (int) $max;
        $id_kec = (int) $id_kec;
        $id_kel = $id_kel;
        $sql = "";
        if ($id_kec > 0) {
            if ($id_kel > 0) {
                $sql = "select s.* from miskin_survey_pokok_rumahtangga s "
                        . "where s.kode_kecamatan=$id_kec "
                        . "and luas_tanah>=$min and luas_tanah<=$max "
                        . "and fid_keluarga in("
                        . "select id_rt from miskin_rumahtangga where "
                        . "kode_kecamatan=$id_kec and kelurahan=$id_kel"
                        . ") "
                        . "limit $halaman,$limit";
            } else {
                $sql = "select s.* from miskin_survey_pokok_rumahtangga s "
                        . "where s.kode_kecamatan=$id_kec "
                        . "and luas_tanah>=$min and luas_tanah<=$max "
                        . "limit $halaman,$limit";
            }
        } else {
            $sql = "select s.* from miskin_survey_pokok_rumahtangga s "
                    . "where luas_tanah>=$min and luas_tanah<=$max "
                    . "limit $halaman,$limit";
        }
        $q = $this->db->query($sql);
        $kec_id_rt = array();
        foreach ($q->result_array() as $row) {
            $id_kecamatan = $row['kode_kecamatan'];
            if (!isset($kec_id_rt[$id_kecamatan])) {
                $kec_id_rt[$id_kecamatan] = array($row['fid_keluarga']);
            } else {
                $kec_id_rt[$id_kecamatan][] = $row['fid_keluarga'];
            }
        }
        //$sql = "select nama_warga,fid_keluarga from miskin_warga where kode_kecamatan=$kode_kecamatan and hubungan_kk=0 and fid_keluarga in (" . implode(",", $list_id_keluarga) . ")";
        $kec_rt_kk = array();
        foreach ($kec_id_rt as $id_kecamatan => $list_id_rt) {
            $sql = "select nama_warga,fid_keluarga from miskin_warga where kode_kecamatan=$id_kecamatan and hubungan_kk=0 and fid_keluarga in (" . implode(",", $list_id_rt) . ")";
            $q2 = $this->db->query($sql);
            foreach ($q2->result_array() as $row) {
                $kec_rt_kk[$id_kecamatan][$row['fid_keluarga']] = $row['nama_warga'];
            }
        }
        $kec_rt_alamat=array();
        foreach ($kec_id_rt as $id_kecamatan => $list_id_rt) {
            $sql = "select alamat,id_rt,nama_kelurahan from miskin_rumahtangga where kode_kecamatan=$id_kecamatan and id_rt in (" . implode(",", $list_id_rt) . ")";
            $q2 = $this->db->query($sql);
            foreach ($q2->result_array() as $row) {
                $kec_rt_alamat[$id_kecamatan][$row['id_rt']] = array('alamat'=>$row['alamat'],
                    'nama_kelurahan'=>$row['nama_kelurahan']);
            }
        }
        $res = array();
        foreach ($q->result_array() as $row) {
            $n = $row;
            $n['kepala_keluarga'] = $kec_rt_kk[$row['kode_kecamatan']][$row['fid_keluarga']];
            $n['alamat']=$kec_rt_alamat[$row['kode_kecamatan']][$row['fid_keluarga']]['alamat'];
            $n['nama_kelurahan']=$kec_rt_alamat[$row['kode_kecamatan']][$row['fid_keluarga']]['nama_kelurahan'];
            $res[] = $n;
        }
        return $res;
        //print_r($q->result_array());
    }

    function get_list_master_luas_tanah() {
        $sql = "select distinct kode_kategori as kategori_id, nama_kategori as kategori,"
                . "min_val as min, max_val as max "
                . "from miskin_agregat_rumahtangga_luastanah ";
        $q = $this->db->query($sql);
        return $q->result();
    }

}
